﻿/*
 * Created by SharpDevelop.
 * User: Yequ
 * Date: 10/8/2011
 * Time: 3:25 PM
 * 
 */
using System;
using System.Data;
using System.Data.SQLite;
using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Forms;
using System.Configuration;

namespace DAL.sqlStatement
{
	/// <summary>
	/// Description of clsQueryStatement.
	/// </summary>
	public class clsSQLStatement
	{
		#region --- Save Log
		/// <summary>
		/// Logging sql query
		/// </summary>
		private void doLogging(string content)
		{
			string txtLog = content;
            StreamWriter _log;

            if (!File.Exists("sqllogfile.dalog"))
            {
                _log = new StreamWriter("sqllogfile.dalog");
            }
            else
            {
                _log = File.AppendText("sqllogfile.dalog");
            }

            // Write to the file:
            _log.WriteLine(DateTime.Now);
            _log.WriteLine(txtLog);
            _log.WriteLine();

            // Close the stream:
            _log.Close();
		}
		#endregion --- Save Log
	
		#region --- Connection String
		   private string ConnectionString = ConfigurationManager.ConnectionStrings["damdm"].ToString();
		#endregion--- Connection String
		
		#region --- Insert Statement
		    public bool doInsert(String tableName, Dictionary<String, String> data)
		    {
			    String columns = "";
			    String values = "";
			    Boolean returnCode = true;
			    foreach (KeyValuePair<String, String> val in data)
			    {
				    columns += String.Format(" {0},", val.Key.ToString());
				    values += String.Format(" '{0}',", val.Value);
			    }
			    columns = columns.Substring(0, columns.Length - 1);
			    values = values.Substring(0, values.Length - 1);
			    try
			    {
				    this.doExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
			    }
			    catch(Exception fail)
			    {
				    MessageBox.Show(fail.Message);
				    doLogging("Insert failed.");
				    returnCode = false;
			    }
			    doLogging("Insert successfull.");
			    return returnCode;
		
		}
		#endregion --- Insert Statement
		
		#region --- Update Statement
		    public bool doUpdate(String tableName, Dictionary<String, String> data, String where)
		    {
			    String vals = "";
			    Boolean returnCode = true;
			    if (data.Count >= 1)
			    {
				    foreach (KeyValuePair<String, String> val in data)
				    {
					    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
				    }
				    vals = vals.Substring(0, vals.Length - 1);
			    }
			    try
			    {
				    this.doExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
			    }
			    catch
			    {
				    doLogging("Update failed.");
				    returnCode = false;
			    }
			    doLogging("Update successfull.");
			    return returnCode;
			
		    }

		#endregion --- Update Statement
		
		#region --- Select Statement
		    public DataTable doGetDataTable(string query)
		    {
			    DataTable dt = new DataTable();
			    try{
				    SQLiteConnection cnn = new SQLiteConnection(ConnectionString);
	                cnn.Open();
	                SQLiteCommand mycommand = new SQLiteCommand(cnn);
	                mycommand.CommandText = query;
	                SQLiteDataReader reader = mycommand.ExecuteReader();
	                dt.Load(reader);
	                reader.Close();
	                cnn.Close();
			    }
			    catch(Exception e){
				    doLogging("Select failed.");
				    throw new Exception(e.Message);
			    }
			    doLogging("Select successfull.");
			    return dt;
		}
		
		/// <summary>
		/// ExecuteReader and will return a bool value, true or false.
		/// </summary>
		/// <param name="query"></param>
		/// <returns>bool value</returns>
		public bool doGetBoolValue(string query)
		{
			try{
				SQLiteConnection conn = new SQLiteConnection(ConnectionString);
				conn.Open();
				SQLiteCommand cmd = new SQLiteCommand(conn);
				cmd.CommandText=query;
				SQLiteDataReader reader = cmd.ExecuteReader();
				if(reader.Read())
				{
					reader.Close();
					return true;
				}
				else
				{
					reader.Close();
					return false;
				}
				
			}
			catch(Exception ex){
				doLogging("Select bool failed");
				throw new Exception(ex.Message);
			}
			
		}
		#endregion --- Select Statement
		
		#region --- Default Query
		    private int doExecuteNonQuery(string query)
	        {
	            SQLiteConnection cnn = new SQLiteConnection(ConnectionString);
	            cnn.Open();
	            SQLiteCommand mycommand = new SQLiteCommand(cnn);
	            mycommand.CommandText = query;
	            int rowsUpdated = mycommand.ExecuteNonQuery();
	            cnn.Close();
	            return rowsUpdated;
	        }
		
		    private string doExecuteScalar(string query)
	        {
	            SQLiteConnection cnn = new SQLiteConnection(ConnectionString);
	            cnn.Open();
	            SQLiteCommand mycommand = new SQLiteCommand(cnn);
	            mycommand.CommandText = query;
	            object value = mycommand.ExecuteScalar();
	            cnn.Close();
	            if (value != null)
	            {
	                return value.ToString();
	            }
	            return "";
	        }
		#endregion --- Default Query
	}
}
